package org.wwy.demo.excel.test;

import com.github.pjfanning.xlsx.StreamingReader;
import org.apache.commons.io.FileUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import java.io.*;
import java.text.DecimalFormat;
import java.util.List;


/**
 * Author: Dreamer-1
 * Date: 2019-03-01
 * Time: 10:21
 * Description: 读取Excel内容
 */
public class ExcelReader4Poi5 {
    

    static Logger log = LoggerFactory.getLogger(ExcelReader4Poi5.class);
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     * @param inputStream 读取文件的输入流
     * @param fileType 文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     * @throws IOException
     */
    public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException, InvalidFormatException {
        Workbook workbook = null;
        /*if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }*/
        workbook = WorkbookFactory.create(inputStream);
        //流式处理
//        workbook = StreamingReader.builder()
//                .rowCacheSize(100)  // 内存中缓存的行数量
//                .bufferSize(4096)   // 文件输入流缓存字节数
//                .open(inputStream);

        return workbook;
    }

    /**
     * 读取Excel文件内容
     * @param fileName 要读取的Excel文件所在路径
     * @return 读取结果列表，读取失败时返回null
     */
    public static void readExcel(String fileName) {

        Workbook workbook = null;
        ByteArrayInputStream inputStream = null;

        try {
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
            // 获取Excel文件
            File excelFile = new File(fileName);
            if (!excelFile.exists()) {
                log.error("指定的Excel文件不存在！");
                return;
            }

            // 获取Excel工作簿
//            inputStream = new FileInputStream(excelFile);
            byte[] bytes = FileUtils.readFileToByteArray(excelFile);
            inputStream = new ByteArrayInputStream(bytes);
            log.info("开始加载workbook");
            workbook = getWorkbook(inputStream, fileType);
            log.info("完成加载workbook");
           parseExcel(workbook);

        } catch (Exception e) {
            log.error("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage(),e);
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                log.error("关闭数据流出错！错误信息：",e);
            }
        }
    }

    public static void reWriteExcel(String fileName,File newFile) {

        Workbook workbook = null;
        ByteArrayInputStream inputStream = null;

        try {
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
            // 获取Excel文件
            File excelFile = new File(fileName);
            if (!excelFile.exists()) {
                log.error("指定的Excel文件不存在！");
                return;
            }

            // 获取Excel工作簿
//            inputStream = new FileInputStream(excelFile);
            byte[] bytes = FileUtils.readFileToByteArray(excelFile);
            inputStream = new ByteArrayInputStream(bytes);
            workbook = getWorkbook(inputStream, fileType);

            writeFile(workbook,newFile);

        } catch (Exception e) {
            log.error("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage(),e);
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                log.error("关闭数据流出错！错误信息：",e);
            }
        }
    }

    /**
     * 解析Excel数据
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    private static void parseExcel(Workbook workbook) {
        // 解析sheet
        int numberOfSheets = workbook.getNumberOfSheets();
        log.info("共{}个sheet",numberOfSheets);
        for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            log.info("开始读取sheet:{}",sheet.getSheetName());

            // 获取第一行数据
            int firstRowNum = 0;

            // 解析每一行的数据，构造数据对象
            int rowNum = 0;
            int lastRowNum = sheet.getLastRowNum();
            log.info("获取到数据行号{}--{}",sheet.getFirstRowNum(),lastRowNum);
            for (Row row : sheet) {

                if (null == row) {
                    continue;
                }

                log.info("========================第{}行数据========================",rowNum);
                convertRowToData(row);
                rowNum++;
            }
            log.info("遍历行结束");

        }
        /*List<? extends PictureData> allPictures = workbook.getAllPictures();
        for (PictureData picture : allPictures) {
            log.info("图片:{}",picture);
        }*/


    }


    /**
     * 解析Excel数据
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    private static void writeFile(Workbook workbook,File newFile) {
        try (FileOutputStream fos = new FileOutputStream(newFile)) {
            workbook.write(fos);
            log.info("写入文件:{}",newFile.getAbsoluteFile());
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

    /**
     * 将单元格内容转换为字符串
     * @param cell
     * @return
     */
    private static Object convertCellValueToString(Cell cell,CellType valueType) {
        if(cell==null){
            return null;
        }
        log.info("type:{}",valueType);
        if (CellType.NUMERIC == valueType) {
            Object numericCellValue;
            if(DateUtil.isCellDateFormatted(cell)){
                numericCellValue = cell.getDateCellValue();
                log.info("日期类型,显示值:{}",numericCellValue);
            }else{
                numericCellValue = cell.getNumericCellValue();
                log.info("原始获取数据:{}",numericCellValue);
                log.info("是否等于0:{}", cell.getNumericCellValue()==0);
                log.info("显示值:{}",new DataFormatter().formatCellValue(cell));
            }
            if(String.valueOf(cell.getNumericCellValue()).contains("E")){
                String value = new DecimalFormat(getDecimalLength(numericCellValue)).format(numericCellValue);
                return value;
            }

            return numericCellValue;
        }else if (CellType.FORMULA == valueType){
//            return cell.getCellFormula();
            CellType cachedFormulaResultType = cell.getCachedFormulaResultType();
            Object s = convertCellValueToString(cell, cachedFormulaResultType);
            log.info("函数类型,继续计算,当前缓存值:{}",s);
            FormulaEvaluator formulaEvaluator = getFormulaEvaluator(cell.getSheet().getWorkbook().getCreationHelper());
            CellValue evaluate = formulaEvaluator.evaluate(cell);
            CellType cellType = evaluate.getCellType();
            log.info("计算值类型:{}",cellType);
            if(cellType == CellType.NUMERIC){
                double numericCellValue = cell.getNumericCellValue();
                if(DateUtil.isCellDateFormatted(cell)){
                    return DateUtil.getJavaDate(evaluate.getNumberValue());
                }else if(String.valueOf(numericCellValue).contains("E")){
                    //科学计数法处理
                    String value = new DecimalFormat(getDecimalLength(numericCellValue)).format(numericCellValue);
                    return value;
                }
                return evaluate.getNumberValue();
            }else if(cellType == CellType.ERROR){
                //计算失
                log.info("计算失败,错误码:{},可取缓存值",evaluate.getErrorValue());
                log.info("此时缓存类型:{}",cell.getCachedFormulaResultType());
                return null;
            }else{
                return evaluate.getStringValue();
            }
        }
        return cell.getStringCellValue();
    }

    private static FormulaEvaluator getFormulaEvaluator(CreationHelper helper) {
        FormulaEvaluator formulaEvaluator = helper.createFormulaEvaluator();
        return formulaEvaluator;
    }

    /**
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     *
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    private static void convertRowToData(Row row) {

        Cell cell;
        for(int i=0;i<20;i++){
            cell = row.getCell(i);
            if(cell != null){
                try{
                    CellType valueType = cell.getCellType();
                    Object value = convertCellValueToString(cell,valueType);
                    log.info("单元格:{},列序号:{}。值:{}",cell.getAddress(),cell.getAddress().getColumn(),value);
                }catch (Exception exception){
                    log.error("解析失败,单元格:{},列序号:{}",cell.getAddress(),cell.getAddress().getColumn(),exception);
                }
                log.info("----------------------------------------------");
            }

        }


    }


    public static String getDecimalLength(Object source) {
        StringBuilder sbr = new StringBuilder("#");
        try {
            String sourceStr = String.valueOf(source);
            int eIndex = sourceStr.indexOf("E");
            if (eIndex == -1) {
                return "#";
            }
            int decimalNumber = eIndex - sourceStr.indexOf(".") - 1;
            int length = Integer.parseInt(sourceStr.substring(eIndex + 1));
            int k = decimalNumber - length;
            if (k <= 0) {
                return "#";
            }
            sbr.append(".");
            for (int i = k; k > 0; k--) {
                sbr.append("#");
            }
        } catch (Exception e) {
            log.info("获取小数位出现异常", e);
            return "#";
        }

        return sbr.toString();
    }

    public static void main(String[] args) {
//        readExcel("D:\\workspace\\demos\\poi3demo\\src\\test\\resources\\010969_华夏安阳6个月持有期混合型证券投资基金_港股通风控资金账户调整单_20241121.xls");
//        readExcel("D:\\workspace\\demos\\poi3demo\\src\\test\\resources\\上饶银行产品标准模板--公募基金-财通资管.xlsx");
        String path = "e:/files/兴业证券-华夏资本-中证1000龙腾宁惠28号集合资产管理计划-场外交易估值报告-2025-02-28.xlsx";
        path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-03\\指令付费明细_2025年3月13日.xlsx";
        path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-03\\2025-02-21申万宏源_华夏资本福星鑫享恒科ETF鲨鱼鳍13号集合资产管理计划_ _估值报告(1).xlsx";
//        path = "e:\\8.融资类业务债券质押台账.xlsx";
        path = "d:/test1.xlsx";
        path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-03\\理财存续台账（20250228).xlsx";
//        path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-03\\原值.xlsx";
        path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-04\\理财存续台账（20250331).xlsx";
//        path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-04\\600258_中航证券启航饶盈1号单一资产管理计划_证券投资基金估值表[2025-03-28].xls";
//        path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-04\\广发银行-华盈海外债2号3号.xlsx";
        path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-05\\理财存续台账（20250430）.xlsx";
//        path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-05\\理财存续台账（20250430）(1).xlsx";
        path = "D:\\\\qwfiles\\\\WXWork\\\\1688853227890455\\\\Cache\\\\File\\\\2025-05\\\\副本外包收入规模202503-1.xlsx";
        path = "d:/2025-04-18申万宏源_华夏资本-鸿鹄宁禧3号集合资产管理计划__估值报告2.xlsx";
        path = "e:/20250513GCS2025051300004390042.xls";
        path = "e:/20250513GCS2025051300004390042-stamp2.xls";
        path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-03\\华夏中证A500ETF对账单20250228.xlsx";
        path = "d:/质押式回购成交单_kevin.xlsx";
        path = "f:/兴业证券-华夏资本鸿鹄鑫享103号集合资产管理计划-场外交易估值报告-2025-05-29.xlsx";
//        path = "f:\\kevin\\Desktop\\德汇交易明细20240419-204136.xls";
        path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-06\\报表_NJ0129_华夏-中国中金财富证券有限公司企业年金计划投资资产（3891—147771）_20250625_2025062500000027754.xlsx";
        path = "e:/test1.xlsx";
        path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-07\\2025-07-02西部证券-华夏资本龙腾鑫享188号-估值表本.xlsx";
        path = "D:\\xls\\资金调节表_华夏基金海港利率债单一资产管理计划.xlsx";
        String basePath = "D:\\xls\\";
        path = basePath + "资金调节表-JJC004_华夏基金京盈单一资产管理计划-2025-07-08.xlsx";
        path = basePath + "资金调节表-JJC004_华夏基金京盈单一资产管理计划-2025-07-08 - 副本.xlsx";
        path = basePath + "资金调节表-华夏量化优选股票型证券投资基金-2025-07-08.xls";
        path = basePath + "资金调节表-华夏中证全指运输交易型开放式指数证券投资基金-2025-07-08.xls";
        path = basePath + "资金调节表-华夏中证全指运输交易型开放式指数证券投资基金发起式联接基金-2025-07-08.xls";
        path = basePath + "资金调节表-华夏资本鸿鹄邮利1号集合资产管理计划.xls";
        path = basePath + "资金调节表-华夏资本鸿鹄邮利10号集合资产管理计划.xls";

        readExcel(path);
    }

    @Test
    public void test4(){
        String path = "D:\\qwfiles\\WXWork\\1688853227890455\\Cache\\File\\2025-03\\2025-02-21申万宏源_华夏资本福星鑫享恒科ETF鲨鱼鳍13号集合资产管理计划_ _估值报告(1).xlsx";

        reWriteExcel(path,new File("d:/test1.xlsx"));
    }

    @Test
    public void test2() throws IOException {
        try (
                InputStream is = new FileInputStream("/path/to/workbook.xlsx");
                Workbook workbook = StreamingReader.builder()
                        .rowCacheSize(100)  // 内存中缓存的行数量
                        .bufferSize(4096)   // 文件输入流缓存字节数
                        .open(is)
        ){
            for (Sheet sheet : workbook){
                System.out.println(sheet.getSheetName());
                for (Row r : sheet) {
                    for (Cell c : r) {
                        System.out.println(c.getStringCellValue());
                    }
                }
            }
        }
    }
}
